<?php

class Accounting_model extends CI_Model {

	function __construct()
		parent::__construct();
	}

	/*
	 * Gets all the accounting entries based on filters, if empty get all.
	 */
	function get_acctg_entries( $filter='', $acctg_entry_id='' ) {
		$return = array();
		
		$sql_query = 
			"SELECT
				ae.acctg_entry_id,
				ae.entry_date,
				ae.work_id,
				ae.description,
				ae.acctg_account_id,
				ac.acctg_account_category_id,
				at.transaction_date,
				w.customer_po_num,
				w.customer_rfq_num,
				w.new_po_num,
				w.new_rfq_num,
				cc.name as company_name,
				ar.name as receipt_name,
				ard.name as receipt_detail_name,
				ardtt.value as receipt_detail_value
			FROM
				acctg_entries AS ae
			LEFT JOIN
				acctg_accounts AS ac ON (ac.acctg_account_id = ae.acctg_account_id)
			LEFT JOIN
				acctg_transactions AS at ON (at.acctg_entry_id = ae.acctg_entry_id)
			LEFT JOIN
				works AS w ON (ae.work_id = w.work_id)
			LEFT JOIN
				contact_companies AS cc ON (w.contact_company_id = cc.contact_id)
			LEFT JOIN
				acctg_receipt_details_to_transactions AS ardtt ON (ardtt.acctg_transaction_id = at.acctg_transaction_id)
			LEFT JOIN
				acctg_receipt_details AS ard ON (ardtt.acctg_receipt_detail_id = ard.acctg_receipt_detail_id)			
			LEFT JOIN
				acctg_receipts AS ar ON (ard.acctg_receipt_id = ar.acctg_receipt_id) ";		

		if(!empty($acctg_entry_id)) {
			$sql_query .= "WHERE ae.acctg_entry_id = '". $acctg_entry_id ."' ";
		}
		
		$sql_query .= "GROUP BY 
							ae.acctg_entry_id
						ORDER BY
							ae.acctg_entry_id DESC
						";
			
		//execute query
		log_message('debug','sql query : '. $sql_query);
		$acctg_entries = $this->db->query($sql_query)->result();			
		log_message('debug','acctg_entries : '. print_r($acctg_entries,true));
		
		foreach($acctg_entries as $acctg_entry_obj) {
			$return[$acctg_entry_obj->acctg_entry_id]['acctg_entry_id'] = $acctg_entry_obj->acctg_entry_id;
			$return[$acctg_entry_obj->acctg_entry_id]['entry_date'] = $acctg_entry_obj->entry_date;
			$return[$acctg_entry_obj->acctg_entry_id]['work_id'] = $acctg_entry_obj->work_id;
			$return[$acctg_entry_obj->acctg_entry_id]['description'] = $acctg_entry_obj->description;
			$return[$acctg_entry_obj->acctg_entry_id]['acctg_account_id'] = $acctg_entry_obj->acctg_account_id;
			$return[$acctg_entry_obj->acctg_entry_id]['acctg_account_category_id'] = $acctg_entry_obj->acctg_account_category_id;
			$return[$acctg_entry_obj->acctg_entry_id]['transaction_date'] = $acctg_entry_obj->transaction_date;
			$return[$acctg_entry_obj->acctg_entry_id]['customer_po_num'] = $acctg_entry_obj->customer_po_num;
			$return[$acctg_entry_obj->acctg_entry_id]['customer_rfq_num'] = $acctg_entry_obj->customer_rfq_num;
			$return[$acctg_entry_obj->acctg_entry_id]['new_po_num'] = $acctg_entry_obj->customer_rfq_num;
			$return[$acctg_entry_obj->acctg_entry_id]['new_rfq_num'] = $acctg_entry_obj->customer_rfq_num;
			$return[$acctg_entry_obj->acctg_entry_id]['company_name'] = $acctg_entry_obj->customer_rfq_num;
			
			$return[$acctg_entry_obj->acctg_entry_id]['official_receipt_num'] = '';
			$return[$acctg_entry_obj->acctg_entry_id]['delivery_receipt_num'] = '';
			if($acctg_entry_obj->receipt_detail_name == 'Official Receipt Number') {
				$return[$acctg_entry_obj->acctg_entry_id]['official_receipt_num'] = $acctg_entry_obj->receipt_detail_value;
			}
			else if($acctg_entry_obj->receipt_detail_name == 'Delivery Receipt Number') {
				$return[$acctg_entry_obj->acctg_entry_id]['delivery_receipt_num'] = $acctg_entry_obj->receipt_detail_value;
			}
		}
		
		//get all transactions and its corresponding info
		if(!empty($acctg_entry_id)) {
			$sql_query =
				"SELECT * FROM acctg_transactions AS at WHERE at.acctg_entry_id = '". $acctg_entry_id ."' ORDER BY at.acctg_transaction_id ASC";

			log_message('debug','sql query : '. $sql_query);
			$transactions = $this->db->query($sql_query)->result();			
			log_message('debug','acctg_entries : '. print_r($transactions,true));
			
			$return[$acctg_entry_obj->acctg_entry_id]['transactions'] = array();
			foreach($transactions as $acctg_transaction_obj) {
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['transaction_type'] = $acctg_transaction_obj->transaction_type;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['transaction_date'] = $acctg_transaction_obj->transaction_date;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['amount_before_vat'] = $acctg_transaction_obj->amount_before_vat;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['vat'] = $acctg_transaction_obj->vat;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['withholding_tax'] = $acctg_transaction_obj->withholding_tax;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['total_amount'] = $acctg_transaction_obj->total_amount;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['acctg_transaction_identifier_id'] = $acctg_transaction_obj->acctg_transaction_identifier_id;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['notes'] = $acctg_transaction_obj->notes;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['total_amount'] = $acctg_transaction_obj->total_amount;
				$return[$acctg_entry_obj->acctg_entry_id]['transactions'][$acctg_transaction_obj->acctg_transaction_id]['total_amount'] = $acctg_transaction_obj->total_amount;
			}
		}
		
		log_message('debug','return : '. print_r($return,true));
		return $return;
	}
	
	/*
	 * Inserts a new accounting entry to the database.
	 */
	function add_acctg_entry( $data ) {
		//start a database transaction
		$this->db->trans_start();
		
		//add accounting entry
		$acctg_entry_query =		
			"INSERT INTO 
				acctg_entries(
					entry_date,
					work_id, 
					description, 
					acctg_account_id
				)
			VALUES(
				CURDATE(), 
				'". $data['work_id'] ."', 
				'". $data['description'] ."', 
				'". $data['acctg_account_id'] ."')";
				
		//execute query
		log_message('debug','sql query : '. $acctg_entry_query);
		$this->db->query($acctg_entry_query);
		
		//get entry id
		$acctg_entry_id = $this->db->insert_id();
		//$acctg_entry_id = $this->db->mysql_insert_id();
		log_message('debug','sql acctg_entry_id : '. $acctg_entry_id);
		
		//get transactions
		$transactions = $data['transactions'];
		
		//check transaction count
		if( count($transactions) > 0) {
			
			//add transactions of the added entry
			$transaction_type = '';
					  
			//get values
			foreach($transactions as $transaction_id => $transaction_data ) {
				log_message('debug','huhu: transaction_id : '. $transaction_id);
				log_message('debug','huhu: '. print_r($transaction_data,true));		
			
				//transaction_query
				$transaction_query = "INSERT INTO acctg_transactions(
							acctg_entry_id, 
							transaction_type, 
							transaction_date, 
							amount_before_vat, 
							vat, 
							withholding_tax,
							total_amount,
							acctg_transaction_identifier_id,
							notes) 
						  VALUES ";				
				
				//get transsaction type
				$transaction_type = $this->get_transaction_type($transaction_id);
				log_message('debug','huhu: transaction_type : '. $transaction_type);
				
				if($transaction_type === 'money in' ||					
				   $transaction_type === 'money out') {
					//MONEY IN or MONEY OUT transaction
					$transaction_query .= "( '". $acctg_entry_id . "', '"
											   . $transaction_type . "', '"
											   . $transaction_data['transaction_date'] . "', '"
											   . $transaction_data['amount_before_vat'] . "', '"
											   . $transaction_data['vat'] . "', '"
											   . $transaction_data['withholding_tax'] . "', '"
											   . $transaction_data['total_amount'] . "', '"
											   . $transaction_data['identifier_id'] . "', '"
											   . $transaction_data['notes'] . 
										  "')";		
				}
				else {
					//OTHER transaction has no Amount fields
					$transaction_query .= "( '". $acctg_entry_id . "', '"
											   . $transaction_type . "', '"
											   . $transaction_data['transaction_date'] . 
											   "', '0', '0', '0', '0', '"
											   . $transaction_data['identifier_id'] . "', '"
											   . $transaction_data['notes'] . 
										  "')";
				}
			
				//execute query
				log_message('debug','sql query : '. $transaction_query);
				$this->db->query($transaction_query);	
			
				//get id of this transaction entry
				$acctg_transaction_id = $this->db->insert_id();
				log_message('debug','sql acctg_transaction_id : '. $acctg_transaction_id);
				
				//add payment method details for the added transaction MONEY IN or MONEY OUT
				if($transaction_type === 'money in' ||					
				   $transaction_type === 'money out') {
					   
					$payment_method_details = $transaction_data['payment_method_details'];
					log_message('debug','payment_method_details : '. print_r($payment_method_details,true));
					
					if(count($payment_method_details) > 0) {
						$payment_method_query = 
								"INSERT INTO acctg_payment_method_details_to_transactions 
								 VALUES ";
								 
						//get data per detail
						foreach($payment_method_details as $payment_method_detail_id => $value) {
							$payment_method_query .= "('". $acctg_transaction_id ."','"
														 . $payment_method_detail_id ."','"
														 . $value ."'),";
						}
						//remove the last comma
						$payment_method_query = substr($payment_method_query, 0, strlen($payment_method_query) - 1 );
						
						//execute query
						log_message('debug','payment_method_query : '. $payment_method_query);
						$this->db->query($payment_method_query);	
					}
				}
				
				//add transaction receipt details for the added transaction MONEY IN, MONEY OUT , or OTHER
				$acctg_receipt_details = $transaction_data['acctg_receipt_details'];
				log_message('debug','acctg_receipt_details : '. print_r($acctg_receipt_details,true));
				
				if(count($acctg_receipt_details) > 0) {
					$acctg_receipt_query = 
							"INSERT INTO acctg_receipt_details_to_transactions  
							 VALUES ";
							 
					//get data per detail 		 
					foreach($acctg_receipt_details as $acctg_receipt_detail_id => $value) {
						$acctg_receipt_query .= "('". $acctg_transaction_id ."','"
													 . $acctg_receipt_detail_id ."','"
													 . $value ."'),";
					}
					//remove the last comma
					$acctg_receipt_query = substr($acctg_receipt_query, 0, strlen($acctg_receipt_query) - 1 );
					
					//execute query
					log_message('debug','acctg_receipt_query : '. $acctg_receipt_query);
					$this->db->query($acctg_receipt_query);	
				}
			}
		}
		
		//complete the transaction
		//i.e. if all queries above is successful, commit everything.
		//if any of the queries above failed, rollback everything
		$this->db->trans_complete();
		
		//return success or failure (true/false)
		return $this->db->trans_status();
	}
	
	/*
	 * Updates an accounting entry in the database.
	 */
	function update_acctg_entry( $data ) {
		//start a database transaction
		$this->db->trans_start();
		
		//add accounting entry
		$acctg_entry_query =		
			"UPDATE 
				acctg_entries
			 SET
				work_id = '". $data['work_id'] ."', 
				description = '". $data['description'] ."',  
				acctg_account_id = '". $data['acctg_account_id'] ."'
			 WHERE
			 	acctg_entry_id = '". $data['acctg_entry_id'] ."'
			";
				
		//execute query
		log_message('debug','sql query : '. $acctg_entry_query);
		$this->db->query($acctg_entry_query);
		
		//get entry id
		$acctg_entry_id = $this->db->insert_id();
		//$acctg_entry_id = $this->db->mysql_insert_id();
		log_message('debug','sql acctg_entry_id : '. $acctg_entry_id);
		
		//get transactions
		$transactions = $data['transactions'];
		
		//check transaction count
		if( count($transactions) > 0) {
			
			//add transactions of the added entry
			$transaction_type = '';
					  
			//get values
			foreach($transactions as $transaction_id => $transaction_data ) {
				log_message('debug','huhu: transaction_id : '. $transaction_id);
				log_message('debug','huhu: '. print_r($transaction_data,true));		
			
				//transaction_query
				$transaction_query = "INSERT INTO acctg_transactions(
							acctg_entry_id, 
							transaction_type, 
							transaction_date, 
							amount_before_vat, 
							vat, 
							withholding_tax,
							total_amount,
							acctg_transaction_identifier_id,
							notes) 
						  VALUES ";				
				
				//get transsaction type
				$transaction_type = $this->get_transaction_type($transaction_id);
				log_message('debug','huhu: transaction_type : '. $transaction_type);
				
				if($transaction_type === 'money in' ||					
				   $transaction_type === 'money out') {
					//MONEY IN or MONEY OUT transaction
					$transaction_query .= "( '". $acctg_entry_id . "', '"
											   . $transaction_type . "', '"
											   . $transaction_data['transaction_date'] . "', '"
											   . $transaction_data['amount_before_vat'] . "', '"
											   . $transaction_data['vat'] . "', '"
											   . $transaction_data['withholding_tax'] . "', '"
											   . $transaction_data['total_amount'] . "', '"
											   . $transaction_data['identifier_id'] . "', '"
											   . $transaction_data['notes'] . 
										  "')";		
				}
				else {
					//OTHER transaction has no Amount fields
					$transaction_query .= "( '". $acctg_entry_id . "', '"
											   . $transaction_type . "', '"
											   . $transaction_data['transaction_date'] . 
											   "', '0', '0', '0', '0', '"
											   . $transaction_data['identifier_id'] . "', '"
											   . $transaction_data['notes'] . 
										  "')";
				}
			
				//execute query
				log_message('debug','sql query : '. $transaction_query);
				$this->db->query($transaction_query);	
			
				//get id of this transaction entry
				$acctg_transaction_id = $this->db->insert_id();
				log_message('debug','sql acctg_transaction_id : '. $acctg_transaction_id);
				
				//add payment method details for the added transaction MONEY IN or MONEY OUT
				if($transaction_type === 'money in' ||					
				   $transaction_type === 'money out') {
					   
					$payment_method_details = $transaction_data['payment_method_details'];
					log_message('debug','payment_method_details : '. print_r($payment_method_details,true));
					
					if(count($payment_method_details) > 0) {
						$payment_method_query = 
								"INSERT INTO acctg_payment_method_details_to_transactions 
								 VALUES ";
								 
						//get data per detail
						foreach($payment_method_details as $payment_method_detail_id => $value) {
							$payment_method_query .= "('". $acctg_transaction_id ."','"
														 . $payment_method_detail_id ."','"
														 . $value ."'),";
						}
						//remove the last comma
						$payment_method_query = substr($payment_method_query, 0, strlen($payment_method_query) - 1 );
						
						//execute query
						log_message('debug','payment_method_query : '. $payment_method_query);
						$this->db->query($payment_method_query);	
					}
				}
				
				//add transaction receipt details for the added transaction MONEY IN, MONEY OUT , or OTHER
				$acctg_receipt_details = $transaction_data['acctg_receipt_details'];
				log_message('debug','acctg_receipt_details : '. print_r($acctg_receipt_details,true));
				
				if(count($acctg_receipt_details) > 0) {
					$acctg_receipt_query = 
							"INSERT INTO acctg_receipt_details_to_transactions  
							 VALUES ";
							 
					//get data per detail 		 
					foreach($acctg_receipt_details as $acctg_receipt_detail_id => $value) {
						$acctg_receipt_query .= "('". $acctg_transaction_id ."','"
													 . $acctg_receipt_detail_id ."','"
													 . $value ."'),";
					}
					//remove the last comma
					$acctg_receipt_query = substr($acctg_receipt_query, 0, strlen($acctg_receipt_query) - 1 );
					
					//execute query
					log_message('debug','acctg_receipt_query : '. $acctg_receipt_query);
					$this->db->query($acctg_receipt_query);	
				}
			}
		}
		
		//complete the transaction
		//i.e. if all queries above is successful, commit everything.
		//if any of the queries above failed, rollback everything
		$this->db->trans_complete();
		
		//return success or failure (true/false)
		return $this->db->trans_status();
	}
	
	/*
	 * Gets only the PO#s, RFQ#s, Description from works to be used in creating new accounting entry.
	 */
	function get_work_info_for_acctg_entry($po_rfq_num='') {
		
		$sql_query = 
			"SELECT 
				w.work_id, 
				w.customer_po_num, 
				w.customer_rfq_num, 
				w.new_po_num, 
				w.new_rfq_num, 
				w.description,
				w.contact_company_id,
				c.name as company_name
			 FROM 
			 	works as w,
				contact_companies as c
			 WHERE
			 	(w.customer_po_num = '". $po_rfq_num ."' OR
				w.customer_rfq_num = '". $po_rfq_num ."' OR
			 	w.new_po_num = '". $po_rfq_num ."' OR
				w.new_rfq_num = '". $po_rfq_num . "') AND
				w.contact_company_id = c.contact_id ";
				
		//execute query	
		log_message('debug','get_work_info_for_acctg_entry sql_query : '. $sql_query);
		$work_info = $this->db->query($sql_query)->result_array();
		
//		print_r($work_info[ 0 ]);
//		die();
//TODO: Check for empty result

		return $work_info[ 0 ];
	}
	
	/*
	 * Gets all account categories.
	 */
	function get_account_categories($isDropdown=false) {
		$result = array();
		$account_categories = $this->db->get('acctg_account_categories')->result();
		if ($isDropdown===true) {
			foreach ($account_categories as $obj) {
				$result[$obj->acctg_account_category_id] = $obj->name;
			}
		}
		else
		{
			$result = $account_categories;
		}
		return $result;	
	}
	
	/*
	 * Gets all specific accounts
	 */
	 function get_acctg_accounts($isPerGroup=false) {
		 $result = array();
		 $acctg_accounts = $this->db->get('acctg_accounts')->result();
		 if($isPerGroup===true) {
			 foreach($acctg_accounts as $obj) {
				 $result[$obj->acctg_account_category_id][$obj->acctg_account_id] = $obj->name;
			 }
		 }
		 else {
			 $result = $acctg_accounts;
		 }
//		 print_r($result);
//		 die();
		 return $result;
	 }
	
	/*
	 * Gets all accounting payment methods.
	 */
	function get_acctg_payment_methods($isDropdown=false) {
		$result = array();
		$payment_methods = $this->db->get('acctg_payment_methods')->result();
		if ($isDropdown===true) {
			foreach ($payment_methods as $obj) {
				$result[$obj->acctg_payment_method_id] = $obj->name;
			}
		}
		else
		{
			$result = $payment_methods;
		}
		return $result;	
	}
	
	/*
	 * Gets all accounting payment methods details.
	 */
	function get_acctg_payment_method_details($isDropdown=false) {
		$result = array();
		$payment_method_details = $this->db->get('acctg_payment_method_details')->result();
		if ($isDropdown===true) {
			foreach ($payment_method_details as $obj) {
				$result[$obj->acctg_payment_method_id] = $obj->name;
			}
		}
		else
		{
			$result = $payment_method_details;
		}
		return $result;	
	}	
	
	/*
	 * Gets all payment method details per payment method
	 */
	 function get_payment_method_details_per_method() {
		 $result = array();
		 
		 $payment_method_details = $this->db->get('acctg_payment_method_details')->result();
		 if($payment_method_details) {
			 foreach($payment_method_details as $obj) {
				 $result[$obj->acctg_payment_method_id][$obj->acctg_payment_method_detail_id] = $obj->name;
			 }
		 }
		 
		 return $result;
	 }
	
	/*
	 * Gets all accounting receipts
	 */
	function get_acctg_receipts($isDropdown=false) {
		$result = array();
		$acctg_receipts = $this->db->get('acctg_receipts')->result();
		if ($isDropdown===true) {
			foreach ($acctg_receipts as $obj) {
				$result[$obj->acctg_receipt_id] = $obj->name;
			}
		}
		else
		{
			$result = $acctg_receipts;
		}
		return $result;	
	}	
	
	/*
	 * Gets all accounting receipt details per receipt type
	 */
	 function get_acctg_receipt_details_per_type() {
		 $result = array();
		 
		 $acctg_receipt_details = $this->db->get('acctg_receipt_details')->result();
		 if($acctg_receipt_details) {
			 foreach($acctg_receipt_details as $obj) {
				 $result[$obj->acctg_receipt_id][$obj->acctg_receipt_detail_id][$obj->name] = $obj->field_type;
			 }
		 }
		 
		 return $result;
	 }	
	
	/*
	 * Gets all transaction identifiers.
	 */
	function get_transaction_identifiers($isDropdown=false) {
		$result = array();
		$transaction_identifiers = $this->db->get('acctg_transaction_identifiers')->result();
		if ($isDropdown===true) {
			foreach ($transaction_identifiers as $obj) {
				$result[$obj->acctg_transaction_identifier_id] = $obj->name;
			}
		}
		else
		{
			$result = $transaction_identifiers;
		}
		return $result;	
	}
	
	/*
	 * Utilities
	 */
	function get_transaction_type($transaction_id='') {
		$result = '';
		
		if( strpos($transaction_id, 'MONEY_IN_') !== false ) {
			$result = 'money in';
		}
		else if( strpos($transaction_id, 'MONEY_OUT_') !== false ) {
			$result = 'money out';
		}
		else if( strpos($transaction_id, 'OTHER_') !== false ) {
			$result = 'other';
		}
		
		return $result;
	}
	
}